package br.com.busgps.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import br.com.busgps.bean.Bus;
import br.com.busgps.bean.Location;
import br.com.busgps.connection.ConnectionDB;
import br.com.busgps.dao.LocationDAO;

/**
 * 
 * Classe de conexao com o DB
 * 
 * @author Rodrigo
 * 
 */
public class LocationDAOImpl extends LocationDAO
{
    private static Logger logger = LoggerFactory.getLogger(LocationDAOImpl.class);

    private Connection connection;

    public LocationDAOImpl()
    {
        connection = ConnectionDB.createConnection();
    }

    /*
     * (non-Javadoc)
     * 
     * @see br.com.busgps.dao.BusDAO#insert(br.com.busgps.bean.Bus)
     */
    public boolean insert(Location loc, Long busId)
    {
        PreparedStatement preparedStatement = null;

        try
        {
            if (busId == null)
            {
                return false;
            }

            String sqlInsert = "INSERT INTO busgps.bus_detail(date, latidute, longitude, speed, way, busId) values(?, ?, ?, ?, ?, ?)";

            preparedStatement = connection.prepareStatement(sqlInsert);

            preparedStatement.setDate(1, (Date) loc.getDate());
            preparedStatement.setFloat(2, loc.getLatitude());
            preparedStatement.setFloat(3, loc.getLongitude());
            preparedStatement.setFloat(4, loc.getSpeed());
            preparedStatement.setString(5, loc.getWay());
            preparedStatement.setLong(6, busId);

            preparedStatement.execute(sqlInsert);

            ConnectionDB.closeConnection(connection, preparedStatement);

            return true;
        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar salvar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement);
        }

        return false;
    }

    /*
     * (non-Javadoc)
     * 
     * @see br.com.busgps.dao.BusDAO#update(br.com.busgps.bean.Bus)
     */
    public boolean update(Location loc)
    {
        PreparedStatement preparedStatement = null;

        try
        {
            String sqlUpdate = "UPDATE busgps.bus_detail set date = ?, latidute = ?, longitude = ?, speed = ?, way = ? where id = ?";

            preparedStatement = connection.prepareStatement(sqlUpdate);

            preparedStatement.setDate(1, (Date) loc.getDate());
            preparedStatement.setFloat(2, loc.getLatitude());
            preparedStatement.setFloat(3, loc.getLongitude());
            preparedStatement.setFloat(4, loc.getSpeed());
            preparedStatement.setString(5, loc.getWay());

            preparedStatement.setLong(6, loc.getId());

            preparedStatement.execute(sqlUpdate);

            ConnectionDB.closeConnection(connection, preparedStatement);

            return true;
        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar atualizar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement);
        }

        return false;
    }

    /*
     * (non-Javadoc)
     * 
     * @see br.com.busgps.dao.BusDAO#updateAll(java.util.List)
     */
    public boolean updateAll(List<Location> locs)
    {
        PreparedStatement preparedStatement = null;

        try
        {
            String sqlUpdate = "UPDATE busgps.bus_detail set date = ?, latidute = ?, longitude = ?, speed = ?, way = ? where id = ?";

            preparedStatement = connection.prepareStatement(sqlUpdate);

            for (Location loc : locs)
            {
                preparedStatement.setDate(1, (Date) loc.getDate());
                preparedStatement.setFloat(2, loc.getLatitude());
                preparedStatement.setFloat(3, loc.getLongitude());
                preparedStatement.setFloat(4, loc.getSpeed());
                preparedStatement.setString(5, loc.getWay());

                preparedStatement.setLong(6, loc.getId());

                preparedStatement.execute(sqlUpdate);
            }

            ConnectionDB.closeConnection(connection, preparedStatement);

            return true;
        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar atualizar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement);
        }

        return false;
    }

    /*
     * (non-Javadoc)
     * 
     * @see br.com.busgps.dao.BusDAO#getAll()
     */
    public List<Location> getAll()
    {
        List<Location> buses = new ArrayList<Location>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try
        {
            String sqlSelect = "SELECT * FROM busgps.bus_detail bd join busgps.bus b on bd.busId = b.id";

            preparedStatement = connection.prepareStatement(sqlSelect);

            resultSet = preparedStatement.executeQuery(sqlSelect);

            while (resultSet.next())
            {
                long id = resultSet.getLong("id");
                Date date = resultSet.getDate("date");
                float latitude = resultSet.getFloat("latitude");
                float longitude = resultSet.getFloat("longitude");
                float speed = resultSet.getFloat("speed");
                String way = resultSet.getString("way");

                String ordem = resultSet.getString("ordem");
                String numberBus = resultSet.getString("number");

                Location loc = new Location(id, date, latitude, longitude, speed, way);

                Bus bus = new Bus(ordem, numberBus);

                loc.setBus(bus);

                buses.add(loc);
            }

            ConnectionDB.closeConnection(connection, preparedStatement, resultSet);
        }
        catch (SQLException e)
        {
            logger.error("Erro ao tentar atualizar os dados. " + e);

            ConnectionDB.closeConnection(connection, preparedStatement, resultSet);
        }

        return buses;
    }
}